package com.lagou.dao;

import com.lagou.entity.PageBean;
import com.lagou.entity.Student;
import com.lagou.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    @Override
    public List<Student> studentShow(PageBean pageBean) {
        List<Student> studentList = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 1、获取Connection对象
            connection = DbUtil.getConnetion();
            // 2、使用StringBuilder类型的对象来描述SQL语句的目的在于后续可以改变
            StringBuilder stringBuilder = new StringBuilder("select * from t_student");
            // 3、当输入分页的需求时进行分页查询，于是拼接SQL语句
            if (pageBean != null) {
                stringBuilder.append(" limit "+pageBean.getStart()+","+pageBean.getRow());
            }
            //4、获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(stringBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            //5、将结果集中的数据取出并放到list集合中返回
            studentList = new ArrayList<>();
            while(resultSet.next()){
                Student student = new Student(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("gender"), new Date(resultSet.getDate("birthday").getTime()),
                        resultSet.getString("email"),resultSet.getString("comment"));
                System.out.println(student);
                studentList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return studentList;
    }

    @Override
    public int studentAdd(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();
            Date birthday = student.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            String sql = "insert into t_student values (NULL, '"+student.getName()+
                    "','"+student.getGender()+"','"+ sdf.format(birthday)+ "',' "+
                    student.getEmail()+"','"+ student.getComment()+"');";
            System.out.println(sql);
            System.out.println(student.getGender());
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public int studentDelete(String[] ids) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();

            for(int i=0; i<ids.length; i++) {
                String sql = "delete from t_student where id= '" + ids[i] +"';";
                System.out.println(sql);
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public int studentModify(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DbUtil.getConnetion();
            Date birthday = student.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            String sql = "update t_student set name = '"+student.getName()+
                    "', gender='"+student.getGender()+"', birthday='"+ sdf.format(birthday)+ "', email='"+
                    student.getEmail()+"', comment='"+ student.getComment()+"' where id='"+student.getId()+"';";
            System.out.println(sql);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DbUtil.closeResource(connection, preparedStatement);
        }
        return 1;
    }

    @Override
    public Student studentFind(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            // 1、获取Connection对象
            connection = DbUtil.getConnetion();
            // 2、sql语句
            String sql = "select * from t_student where id='"+id+"';";

            //3、获取PreparedStatement并执行上述SQL语句
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            //4、将结果集中的数据返回
            while(resultSet.next()){
                student = new Student(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("gender"), new Date(resultSet.getDate("birthday").getTime()),
                        resultSet.getString("email"),resultSet.getString("comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return student;
    }
    @Override
    public Student studentFindByClassId(int classId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;

        try {
            //1、获取connection连接对象
            connection = DbUtil.getConnetion();
            // 2、准备sql语句
            String sql = "select * from t_student where class_id=?";
            // 3、获取PreparedStatement并执行sql语句
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, classId);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                student = new Student(resultSet.getInt("id"), resultSet.getString("name"),
                        resultSet.getString("gender"), new java.util.Date(resultSet.getDate("birthday").getTime()),
                        resultSet.getString("email"),resultSet.getString("comment"),
                        resultSet.getInt("class_id"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeResource(connection, preparedStatement, resultSet);
        }
        return student;
    }

}
